BRIC_project

BRIC Project

# clear workspace
##rm(list=ls())

getting the current WD

getwd()

[1] "/Users/Manu/Documents/GitHub/bric_ap_project"

changing the WD

##setwd("/Users/Manu/Desktop/TUM_Master_Mgt_Technology/TUM_SS_21/Empirical Asset Pricing seminar/Seminar Thesis/BRIC_data")

Loading Libraries

# loading libraries
library(data.table) # extension of the data.frame package. It is widely used for fast aggregation of large datasets, low latency add/update/remove of columns, quicker ordered joins, and a fast file reader.

Registered S3 method overwritten by 'data.table':
  method           from
  print.data.table     
data.table 1.14.0 using 1 threads (see ?getDTthreads).  Latest news: r-datatable.com
**********
This installation of data.table has not detected OpenMP support. It should still work but in single-threaded mode.
This is a Mac. Please read https://mac.r-project.org/openmp/. Please engage with Apple and ask them for support. Check r-datatable.com for updates, and our Mac instructions here: https://github.com/Rdatatable/data.table/wiki/Installation. After several years of many reports of installation problems on Mac, it's time to gingerly point out that there have been no similar problems on Windows or Linux.
**********

library(dplyr) # data manipulation package

package ‘dplyr’ was built under R version 3.6.2
Attaching package: ‘dplyr’

The following objects are masked from ‘package:data.table’:

    between, first, last

The following objects are masked from ‘package:stats’:

    filter, lag

The following objects are masked from ‘package:base’:

    intersect, setdiff, setequal, union

library(lubridate)

package ‘lubridate’ was built under R version 3.6.2
Attaching package: ‘lubridate’

The following objects are masked from ‘package:data.table’:

    hour, isoweek, mday, minute, month, quarter, second, wday, week,
    yday, year

The following objects are masked from ‘package:base’:

    date, intersect, setdiff, union

library(zoo) # methods for totally ordered indexed observations. It aims at performing calculations containing irregular time series of numeric vectors, matrices & factors

Attaching package: ‘zoo’

The following objects are masked from ‘package:base’:

    as.Date, as.Date.numeric

library(stats)
library(utils)

Loading in the R.data

Data column descriptions (Worldscope): https://www.professors.wi.tum.de/fileadmin/w00bca/fm/Worldscope_Data_Definition_Guide_Issue_15.pdf

https://docs.google.com/spreadsheets/d/1YtuJiv60Q6nKIaFJLQY60sGQErbsl_8nvPdUHmvO8vM/edit?usp=sharing

# loading R.data BRIC monthly
load("/Users/Manu/Desktop/TUM_Master_Mgt_Technology/TUM_SS_21/Empirical Asset Pricing seminar/Seminar Thesis/BRIC_data/BRIC_monthly.RData")

# loading R.data BRIC monthly
load("/Users/Manu/Desktop/TUM_Master_Mgt_Technology/TUM_SS_21/Empirical Asset Pricing seminar/Seminar Thesis/BRIC_data/BRIC_static.RData")

# loading R.data BRIC monthly
load("/Users/Manu/Desktop/TUM_Master_Mgt_Technology/TUM_SS_21/Empirical Asset Pricing seminar/Seminar Thesis/BRIC_data/BRIC_yearly.RData")

str(BRIC.monthly)

Classes ‘data.table’ and 'data.frame':  5525758 obs. of  13 variables:
 $ Id     : Factor w/ 96201 levels "130022","130038",..: 17 17 17 17 17 17 17 17 17 17 ...
 $ country: chr  "BRA" "BRA" "BRA" "BRA" ...
 $ Date   : Date, format: "1979-12-31" "1980-01-31" ...
 $ MV     : num  NA NA NA NA NA NA NA NA NA NA ...
 $ MV.USD : num  NA NA NA NA NA NA NA NA NA NA ...
 $ RET    : num  NA NA NA NA NA NA NA NA NA NA ...
 $ RET.USD: num  NA NA NA NA NA NA NA NA NA NA ...
 $ PCH    : num  NA NA NA NA NA NA NA NA NA NA ...
 $ PCH.USD: num  NA NA NA NA NA NA NA NA NA NA ...
 $ UP     : num  NA NA NA NA NA NA NA NA NA NA ...
 $ NOSH   : num  NA NA NA NA NA NA NA NA NA NA ...
 $ AF     : num  NA NA NA NA NA NA NA NA NA NA ...
 $ ym     : 'yearmon' num  Dec 1979 Jan 1980 Feb 1980 Mar 1980 ...
 - attr(*, ".internal.selfref")=<externalptr> 
 - attr(*, "sorted")= chr "Id"

Importing the Risk free Rate Data

download T-bill data from Kenneth French Website for the risk free rate

# import the Kenneth/French risk free rate (1month T-bill rate)
kf_data <- read.csv("/Users/Manu/Desktop/TUM_Master_Mgt_Technology/TUM_SS_21/Empirical Asset Pricing seminar/Seminar Thesis/BRIC_data/F_F_Research_Data_Factors.csv")

kf_data

# extracting the risk free rate
one_m_tbill <- kf_data$RF


tail(kf_data)

NA

# summary stats
summary(BRIC.monthly)

       Id            country               Date                  MV         
 130117 :    469   Length:5525758     Min.   :1979-12-31   Min.   :      0  
 130139 :    469   Class :character   1st Qu.:1989-09-29   1st Qu.:    108  
 130143 :    469   Mode  :character   Median :1999-06-30   Median :   1060  
 130147 :    469                      Mean   :1999-06-30   Mean   :  14435  
 130165 :    469                      3rd Qu.:2009-03-31   3rd Qu.:   4416  
 130169 :    469                      Max.   :2018-12-31   Max.   :8524587  
 (Other):5522944                                           NA's   :4035736  
     MV.USD              RET             RET.USD             PCH         
 Min.   :0.00e+00   Min.   :-100      Min.   :-100      Min.   :  -100   
 1st Qu.:3.00e+00   1st Qu.:  -7      1st Qu.:  -8      1st Qu.:    -8   
 Median :4.40e+01   Median :   0      Median :   0      Median :     0   
 Mean   :1.14e+05   Mean   :   2      Mean   :   2      Mean   :     3   
 3rd Qu.:4.30e+02   3rd Qu.:   7      3rd Qu.:   8      3rd Qu.:     7   
 Max.   :2.90e+10   Max.   : 888      Max.   : 903      Max.   :599900   
 NA's   :4035736    NA's   :4044505   NA's   :4044505   NA's   :4042312  
    PCH.USD              UP               NOSH                 AF         
 Min.   :  -100    Min.   :      0   Min.   :0.000e+00   Min.   :     0   
 1st Qu.:    -8    1st Qu.:      4   1st Qu.:5.800e+03   1st Qu.:     1   
 Median :     0    Median :     12   Median :2.500e+04   Median :     1   
 Mean   :     2    Mean   :    285   Mean   :3.569e+06   Mean   :    14   
 3rd Qu.:     7    3rd Qu.:     39   3rd Qu.:2.221e+05   3rd Qu.:     1   
 Max.   :442446    Max.   :3515086   Max.   :1.296e+10   Max.   :225326   
 NA's   :4041888   NA's   :3529642   NA's   :3737333     NA's   :3757210  
       ym      
 Min.   :1980  
 1st Qu.:1990  
 Median :1999  
 Mean   :1999  
 3rd Qu.:2009  
 Max.   :2019  
               

str(BRIC.static)

Classes ‘data.table’ and 'data.frame':  11783 obs. of  41 variables:
 $ GEOGN  : chr  "BRAZIL" "BRAZIL" "BRAZIL" "BRAZIL" ...
 $ GEOLN  : chr  "BRAZIL" "BRAZIL" "BRAZIL" "BRAZIL" ...
 $ NAME   : chr  "ACOS VILL ON DEAD - DELIST07/02/11" "CASA ANGLO ON 1000 DEAD - BRUPT.28/03/00" "CENTRAIS ELECTRICAS DE SANTA CATAR ON" "AMPLA ENERGIA E SERVICOS ON" ...
 $ ECNAME : chr  "ACOS VILLARES SA" "CASA ANGLO" "CENTRAIS ELETRICAS DE SANTA CATARINA SA" "AMPLA ENERGIA E SERVICOS SA" ...
 $ ENAME  : chr  "ACOS VILL ON DEAD - DELIST07/02/11" "CASA ANGLO ON 1000 DEAD - BRUPT.28/03/00" "CENTRAIS ELECTRICAS DE SANTA CATAR ON" "AMPLA ENERGIA E SERVICOS ON" ...
 $ PNAME  : chr  "ACOS VILL ON 1000" NA NA "AMPRA ENERG ON 1000" ...
 $ TYPE   : chr  "EQ" "EQ" "EQ" "EQ" ...
 $ MAJOR  : chr  "Y" "Y" "Y" "Y" ...
 $ ISINID : chr  "P" "P" "P" "P" ...
 $ GGISN  : chr  "BR" "BR" "BR" "BR" ...
 $ PCUR   : chr  "C" "C" "C" "C" ...
 $ EXNAME : chr  "Sao Paulo" "Sao Paulo" "Sao Paulo" "Sao Paulo" ...
 $ ESTAT  : chr  "DEAD" "DEAD" "ACT." "ACT." ...
 $ ICBSUC : int  1757 5373 7537 7535 3722 7537 2713 3355 8355 3577 ...
 $ INDG   : int  56 87 96 169 59 96 98 63 102 71 ...
 $ INDM   : chr  "Iron & Steel" "Broadline Retailers" "Alt. Electricity" "Con. Electricity" ...
 $ WC06011: int  5291 7020 8220 8220 4992 8592 1320 NA NA 4670 ...
 $ WC07021: int  3312 5311 4911 4911 3965 4911 3721 NA NA 2011 ...
 $ WC07022: int  3316 6141 4924 NA 2844 6719 4581 NA NA 2075 ...
 $ WC07023: int  6719 6021 6719 NA 3421 NA 3728 NA NA 2015 ...
 $ WC07024: int  NA NA NA NA 3561 NA 8249 NA NA NA ...
 $ WC07025: int  NA NA NA NA 3262 NA NA NA NA NA ...
 $ WC07026: int  NA NA NA NA NA NA NA NA NA NA ...
 $ WC07027: int  NA NA NA NA NA NA NA NA NA NA ...
 $ WC07028: int  NA NA NA NA NA NA NA NA NA NA ...
 $ WC07040: chr  "1757" NA "7537" "7535" ...
 $ WC07041: 'integer64' num  2.53e-314 2.64e-314 2.92e-314 2.92e-314 2.67e-314 ...
 $ ISIN   : chr  "BRAVILACNOR1" "BRCABRACNOR3" "BRCLSCACNOR6" "BRCBEEACNOR3" ...
 $ LOC    : chr  "BRAVIL3" "BRCABR3" "BRCLSC3" "BRCBEE3" ...
 $ SECD   : chr  "2006341" "2177225" "B19PKR9" "2160902" ...
 $ MNEM   : chr  "BR:AV3" "BR:CA3" "BR:CL3" "BR:CJ3" ...
 $ A4NAME : chr  NA NA NA NA ...
 $ IBTKR  : chr  "@:@7AE" NA NA "@:@7C8" ...
 $ BDATE  : Date, format: "1990-02-05" "1991-01-30" ...
 $ ESBDT  : Date, format: NA NA ...
 $ TIME   : Date, format: "2011-02-04" "1999-01-28" ...
 $ EDATE  : Date, format: NA NA ...
 $ WC18272: int  1944 1913 NA 1883 NA 19620611 19690819 NA NA NA ...
 $ WC07015: Date, format: "2011-02-07" "2000-03-30" ...
 $ Id     : chr  "130117" "130139" "130143" "130147" ...
 $ country: chr  "BRA" "BRA" "BRA" "BRA" ...
 - attr(*, ".internal.selfref")=<externalptr> 
 - attr(*, "sorted")= chr "GEOGN"

ISIN: International Security Identification Number (stock identifier) ESTAT: active vs inactive company (publicly listed or not) Id: join column with BRIC.yearly dataframe INDM: industry sector code

GEOGN: geographic group name GEOLN: geographic location

List of Database codes: https://www.bwl.uni-mannheim.de/media/Lehrstuehle/bwl/Maug/Database_info/Datastream_dataypes.pdf

str(BRIC.yearly)

Classes ‘data.table’ and 'data.frame':  459498 obs. of  105 variables:
 $ Id     : Factor w/ 96201 levels "130022","130038",..: 17 17 17 17 17 17 17 17 17 17 ...
 $ country: chr  "BRA" "BRA" "BRA" "BRA" ...
 $ ICBSUC : int  1757 1757 1757 1757 1757 1757 1757 1757 1757 1757 ...
 $ WC07021: int  3312 3312 3312 3312 3312 3312 3312 3312 3312 3312 ...
 $ YEAR   : int  1980 1981 1982 1983 1984 1985 1986 1987 1988 1989 ...
 $ W05651 : num  NA NA NA NA NA NA NA NA NA NA ...
 $ W08006 : num  NA NA NA NA NA NA NA NA NA NA ...
 $ WC01001: num  NA NA NA NA NA NA NA 0 0 1 ...
 $ WC01051: num  NA NA NA NA NA NA NA 0 0 0 ...
 $ WC01075: num  NA NA NA NA NA NA NA NA NA NA ...
 $ WC01076: num  NA NA NA NA NA NA NA NA NA NA ...
 $ WC01084: num  NA NA NA NA NA NA NA NA NA NA ...
 $ WC01101: num  NA NA NA NA NA NA NA 0 0 0 ...
 $ WC01151: num  NA NA NA NA NA NA NA 0 0 0 ...
 $ WC01201: num  NA NA NA NA NA NA NA NA NA NA ...
 $ WC01230: num  NA NA NA NA NA NA NA 0 0 0 ...
 $ WC01245: num  NA NA NA NA NA NA NA NA NA NA ...
 $ WC01249: Factor w/ 523677 levels "209033","220729",..: NA NA NA NA NA NA NA 932 932 3653 ...
 $ WC01250: num  NA NA NA NA NA NA NA 0 0 0 ...
 $ WC01251: num  NA NA NA NA NA NA NA 0 0 0 ...
 $ WC01255: num  NA NA NA NA NA NA NA 0 0 0 ...
 $ WC01266: num  NA NA NA NA NA NA NA NA NA NA ...
 $ WC01271: num  NA NA NA NA NA NA NA NA NA NA ...
 $ WC01301: int  NA NA NA NA NA NA NA 0 0 0 ...
 $ WC01401: num  NA NA NA NA NA NA NA 0 0 0 ...
 $ WC01451: num  NA NA NA NA NA NA NA 0 0 0 ...
 $ WC01501: num  NA NA NA NA NA NA NA 0 0 0 ...
 $ WC01505: num  NA NA NA NA NA NA NA 0 0 0 ...
 $ WC01551: num  NA NA NA NA NA NA NA 0 0 0 ...
 $ WC01651: num  NA NA NA NA NA NA NA 0 0 0 ...
 $ WC02001: num  NA NA NA NA NA NA NA 0 0 0 ...
 $ WC02005: num  NA NA NA NA NA NA NA 0 0 0 ...
 $ WC02051: num  NA NA NA NA NA NA NA 0 0 0 ...
 $ WC02099: num  NA NA NA NA NA NA NA 0 0 0 ...
 $ WC02101: num  NA NA NA NA NA NA NA 0 0 0 ...
 $ WC02140: num  NA NA NA NA NA NA NA 0 0 0 ...
 $ WC02201: num  NA NA NA NA NA NA NA 0 0 1 ...
 $ WC02214: int  NA NA NA NA NA NA NA NA NA NA ...
 $ WC02301: num  NA NA NA NA NA NA NA 0 0 3 ...
 $ WC02501: num  NA NA NA NA NA NA NA 0 0 2 ...
 $ WC02999: num  NA NA NA NA NA NA NA 0 0 3 ...
 $ WC03040: num  NA NA NA NA NA NA NA 0 0 0 ...
 $ WC03051: num  NA NA NA NA NA NA NA 0 0 0 ...
 $ WC03054: num  NA NA NA NA NA NA NA 0 0 0 ...
 $ WC03060: num  NA NA NA NA NA NA NA NA NA NA ...
 $ WC03063: num  NA NA NA NA NA NA NA NA NA NA ...
 $ WC03069: num  NA NA NA NA NA NA NA NA NA NA ...
 $ WC03101: num  NA NA NA NA NA NA NA 0 0 1 ...
 $ WC03151: num  NA NA NA NA NA NA NA 0 0 0 ...
 $ WC03251: num  NA NA NA NA NA NA NA 0 0 1 ...
 $ WC03255: num  NA NA NA NA NA NA NA 0 0 1 ...
 $ WC03262: num  NA NA NA NA NA NA NA NA NA NA ...
 $ WC03263: num  NA NA NA NA NA NA NA 0 0 0 ...
 $ WC03351: num  NA NA NA NA NA NA NA 0 0 1 ...
 $ WC03426: num  NA NA NA NA NA NA NA 0 0 1 ...
 $ WC03451: num  NA NA NA NA NA NA NA 0 0 0 ...
 $ WC03480: num  NA NA NA NA NA NA NA 0 0 0 ...
 $ WC03495: num  NA NA NA NA NA NA NA 0 0 0 ...
 $ WC03501: num  NA NA NA NA NA NA NA 0 0 1 ...
 $ WC03995: num  NA NA NA NA NA NA NA 0 0 1 ...
 $ WC03999: num  NA NA NA NA NA NA NA 0 0 3 ...
 $ WC04055: num  NA NA NA NA NA NA NA NA NA NA ...
 $ WC04100: int  NA NA NA NA NA NA NA NA NA NA ...
 $ WC04101: int  NA NA NA NA NA NA NA NA 0 0 ...
 $ WC04199: int  NA NA NA NA NA NA NA NA 0 0 ...
 $ WC04201: num  NA NA NA NA NA NA NA 0 0 0 ...
 $ WC04251: num  NA NA NA NA NA NA NA NA 0 0 ...
 $ WC04551: num  NA NA NA NA NA NA NA 0 0 0 ...
 $ WC04601: num  NA NA NA NA NA NA NA NA 0 0 ...
 $ WC04751: num  NA NA NA NA NA NA NA NA 0 0 ...
 $ WC04825: num  NA NA NA NA NA NA NA NA NA NA ...
 $ WC04826: num  NA NA NA NA NA NA NA NA NA NA ...
 $ WC04827: num  NA NA NA NA NA NA NA NA NA NA ...
 $ WC04828: num  NA NA NA NA NA NA NA NA NA NA ...
 $ WC04829: num  NA NA NA NA NA NA NA NA NA NA ...
 $ WC04830: num  NA NA NA NA NA NA NA NA NA NA ...
 $ WC04860: num  NA NA NA NA NA NA NA NA NA NA ...
 $ WC05001: num  NA NA NA NA NA NA NA NA NA NA ...
 $ WC05101: num  NA NA NA NA NA NA NA 0 0 0 ...
 $ WC05202: num  NA NA NA NA NA NA NA 0 0 0 ...
 $ WC05301: num  NA NA NA NA NA ...
 $ WC05350: Factor w/ 4261 levels "","29.06.1999",..: NA NA NA NA NA NA NA 336 337 140 ...
 $ WC05376: num  NA NA NA NA NA NA NA 0 0 0 ...
 $ WC05401: int  NA NA NA NA NA NA NA 0 0 0 ...
 $ WC05476: num  NA NA NA NA NA NA NA 0 0 0 ...
 $ WC05491: num  NA NA NA NA NA NA NA 0 0 0 ...
 $ WC05505: num  NA NA NA NA NA NA NA NA NA NA ...
 $ WC05508: num  NA NA NA NA NA NA NA NA NA NA ...
 $ WC05651: int  NA NA NA NA NA NA NA NA NA NA ...
 $ WC05905: Factor w/ 9289 levels "","01.03.2017",..: NA NA NA NA NA NA NA NA NA NA ...
 $ WC06798: int  NA NA NA NA NA NA NA NA NA NA ...
 $ WC07011: int  NA NA NA NA NA NA NA 5115 9961 9702 ...
 $ WC07210: num  NA NA NA NA NA NA NA NA NA NA ...
 $ WC08001: num  NA NA NA NA NA NA NA NA NA NA ...
 $ WC08002: num  NA NA NA NA NA NA NA NA NA NA ...
 $ WC08004: num  NA NA NA NA NA NA NA NA NA NA ...
 $ WC08006: num  NA NA NA NA NA NA NA NA NA NA ...
 $ WC08256: num  NA NA NA NA NA ...
 $ WC08301: num  NA NA NA NA NA ...
  [list output truncated]
 - attr(*, ".internal.selfref")=<externalptr> 
 - attr(*, "sorted")= chr "Id"

head(BRIC.yearly)

ID: ?? Country: 4 BRIC COUNTRY CODES ICBSUC: industrial classification benchmark https://link.springer.com/content/pdf/bbm%3A978-3-8350-9531-1%2F1.pdf

WC07021: SIC(standard industrial classification) primary code from Worldscope W05651: Common Shares Traded - Annual (Security)

summary(BRIC.yearly)

       Id           country              ICBSUC         WC07021      
 130117 :    39   Length:459498      Min.   : 533    Min.   : 111    
 130139 :    39   Class :character   1st Qu.:2723    1st Qu.:2821    
 130143 :    39   Mode  :character   Median :3577    Median :3552    
 130147 :    39                      Mean   :4442    Mean   :3915    
 130165 :    39                      3rd Qu.:6535    3rd Qu.:4924    
 130169 :    39                      Max.   :9578    Max.   :9721    
 (Other):459264                      NA's   :13572   NA's   :128973  
      YEAR          W05651              W08006            WC01001          
 Min.   :1980   Min.   :        0   Min.   :       0   Min.   :  -1897610  
 1st Qu.:1989   1st Qu.:    35284   1st Qu.:  149737   1st Qu.:    400216  
 Median :1999   Median :   133897   Median :  532443   Median :   1331283  
 Mean   :1999   Mean   :  2042004   Mean   : 1791848   Mean   :  15112089  
 3rd Qu.:2009   3rd Qu.:   359423   3rd Qu.: 1013663   3rd Qu.:   4894690  
 Max.   :2018   Max.   :119768705   Max.   :33165328   Max.   :6546143000  
                NA's   :459397      NA's   :459436     NA's   :361509      
    WC01051              WC01075              WC01076          
 Min.   :  -2687022   Min.   :     -8532   Min.   :  -6697923  
 1st Qu.:    251251   1st Qu.:      7451   1st Qu.:   1979948  
 Median :    919940   Median :    109322   Median :   9823772  
 Mean   :   9572937   Mean   :  18254570   Mean   :  40426730  
 3rd Qu.:   3461544   3rd Qu.:   6006500   3rd Qu.:  33495928  
 Max.   :4647748500   Max.   :1491146740   Max.   :1452100000  
 NA's   :365827       NA's   :453797       NA's   :457878      
    WC01084             WC01101              WC01151         
 Min.   :   -85997   Min.   : -12517580   Min.   :  -101176  
 1st Qu.:    24908   1st Qu.:     45776   1st Qu.:    13206  
 Median :    95116   Median :    121546   Median :    44810  
 Mean   :  1653017   Mean   :   1243122   Mean   :   699597  
 3rd Qu.:   420679   3rd Qu.:    356292   3rd Qu.:   164658  
 Max.   :716521000   Max.   :1047652000   Max.   :613160000  
 NA's   :389761      NA's   :396735       NA's   :365087     
    WC01201              WC01230              WC01245              WC01249      
 Min.   :   -108996   Min.   :-136406123   Min.   :    -47089   0      :    74  
 1st Qu.:      2153   1st Qu.:      3975   1st Qu.:   3202732   4      :     8  
 Median :     16581   Median :     21238   Median :  10363000   1      :     7  
 Mean   :    294735   Mean   :   1423149   Mean   :  36520834   22     :     5  
 3rd Qu.:     56000   3rd Qu.:    136184   3rd Qu.:  30421235   10     :     5  
 Max.   :3850520500   Max.   :2189000000   Max.   :1053486329   (Other): 72311  
 NA's   :428230       NA's   :388237       NA's   :458157       NA's   :387088  
    WC01250              WC01251             WC01255             WC01266         
 Min.   :-457617800   Min.   :   -37900   Min.   :  -124722   Min.   :  -137794  
 1st Qu.:     11608   1st Qu.:     4730   1st Qu.:        0   1st Qu.:     1168  
 Median :     85880   Median :    26696   Median :        0   Median :     6429  
 Mean   :   1707550   Mean   :   578613   Mean   :    51150   Mean   :   171131  
 3rd Qu.:    377127   3rd Qu.:   127970   3rd Qu.:        0   3rd Qu.:    29816  
 Max.   :1660438000   Max.   :234524200   Max.   :109420000   Max.   :116837000  
 NA's   :362257       NA's   :366104      NA's   :407361      NA's   :394891     
    WC01271             WC01301              WC01401          
 Min.   :-15043600   Min.   :-225335300   Min.   :-289914000  
 1st Qu.:    90680   1st Qu.:         0   1st Qu.:     11424  
 Median :  1249570   Median :         0   Median :     77140  
 Mean   : 10713478   Mean   :    -15993   Mean   :   1632255  
 3rd Qu.:  5688000   3rd Qu.:         0   3rd Qu.:    339827  
 Max.   :679414298   Max.   :    287470   Max.   :1580887000  
 NA's   :457543      NA's   :437442       NA's   :361581      
    WC01451             WC01501             WC01505          
 Min.   :-80575000   Min.   :-27276000   Min.   :-238880000  
 1st Qu.:     1961   1st Qu.:        0   1st Qu.:         0  
 Median :    14743   Median :        0   Median :         0  
 Mean   :   440459   Mean   :    46581   Mean   :     -4889  
 3rd Qu.:    73385   3rd Qu.:      476   3rd Qu.:         0  
 Max.   :498333023   Max.   : 75000000   Max.   :  58460662  
 NA's   :362264      NA's   :362720      NA's   :387330      
    WC01551              WC01651              WC02001          
 Min.   :-417475218   Min.   :-417475218   Min.   :  -2250400  
 1st Qu.:      8181   1st Qu.:      8236   1st Qu.:     37850  
 Median :     58858   Median :     58954   Median :    179046  
 Mean   :   1159198   Mean   :   1160424   Mean   :   2323822  
 3rd Qu.:    255842   3rd Qu.:    256518   3rd Qu.:    632348  
 Max.   :1307018000   Max.   :1307018000   Max.   :2704067300  
 NA's   :361555       NA's   :361560       NA's   :363655      
    WC02005              WC02051              WC02099         
 Min.   :  -2250400   Min.   :    -37626   Min.   :   -15881  
 1st Qu.:     38827   1st Qu.:    101493   1st Qu.:    14578  
 Median :    186226   Median :    315546   Median :    87555  
 Mean   :   4736283   Mean   :   3714914   Mean   :   992293  
 3rd Qu.:    676419   3rd Qu.:   1104569   3rd Qu.:   374200  
 Max.   :3852586000   Max.   :3897521400   Max.   :346800000  
 NA's   :361890       NA's   :363997       NA's   :409409     
    WC02101             WC02140             WC02201              WC02214      
 Min.   :  -235016   Min.   :  -633124   Min.   :   -470864   Min.   :0       
 1st Qu.:    49548   1st Qu.:     5814   1st Qu.:    383350   1st Qu.:0       
 Median :   209621   Median :    29418   Median :   1024932   Median :0       
 Mean   :  2145711   Mean   :   530282   Mean   :   7632966   Mean   :0       
 3rd Qu.:   788038   3rd Qu.:   134776   3rd Qu.:   3137342   3rd Qu.:0       
 Max.   :804364000   Max.   :369266850   Max.   :3993722000   Max.   :0       
 NA's   :364513      NA's   :408806      NA's   :368042       NA's   :459485  
    WC02301              WC02501              WC02999         
 Min.   :-1.808e+07   Min.   :-1.698e+04   Min.   :0.000e+00  
 1st Qu.: 2.741e+05   1st Qu.: 1.395e+05   1st Qu.:7.533e+05  
 Median : 8.610e+05   Median : 4.941e+05   Median :2.103e+06  
 Mean   : 1.538e+07   Mean   : 8.949e+06   Mean   :4.752e+07  
 3rd Qu.: 3.195e+06   3rd Qu.: 1.880e+06   3rd Qu.:7.024e+06  
 Max.   : 1.865e+10   Max.   : 1.255e+10   Max.   :3.605e+10  
 NA's   :380376       NA's   :362537       NA's   :361838     
    WC03040              WC03051             WC03054            WC03060        
 Min.   :  -1378248   Min.   :0.000e+00   Min.   :   -2362   Min.   :       0  
 1st Qu.:     47386   1st Qu.:3.650e+04   1st Qu.:    4455   1st Qu.:   19369  
 Median :    168860   Median :2.407e+05   Median :   16950   Median :  296243  
 Mean   :   1896792   Mean   :5.131e+06   Mean   :  179760   Mean   : 3716895  
 3rd Qu.:    640523   3rd Qu.:9.734e+05   3rd Qu.:   57436   3rd Qu.: 2475000  
 Max.   :1068610000   Max.   :4.900e+09   Max.   :81000000   Max.   :82550000  
 NA's   :370003       NA's   :366101      NA's   :421502     NA's   :458833    
    WC03063             WC03069             WC03101          
 Min.   :-14883633   Min.   : -9979722   Min.   :     -2033  
 1st Qu.:     3145   1st Qu.:        0   1st Qu.:    219724  
 Median :    14198   Median :      630   Median :    701758  
 Mean   :   361552   Mean   :   196955   Mean   :   6280780  
 3rd Qu.:    64350   3rd Qu.:    13829   3rd Qu.:   2421447  
 Max.   :358031550   Max.   :278502000   Max.   :3836000000  
 NA's   :412352      NA's   :415132      NA's   :368049      
    WC03151              WC03251              WC03255             WC03262        
 Min.   :-1.544e+09   Min.   :    -16000   Min.   :0.000e+00   Min.   : -577681  
 1st Qu.: 4.368e+03   1st Qu.:         0   1st Qu.:6.925e+04   1st Qu.:       0  
 Median : 2.089e+05   Median :     69565   Median :4.035e+05   Median :       0  
 Mean   : 1.355e+06   Mean   :   6591599   Mean   :1.147e+07   Mean   :   85341  
 3rd Qu.: 8.157e+05   3rd Qu.:    715466   3rd Qu.:1.871e+06   3rd Qu.:       0  
 Max.   : 1.869e+09   Max.   :3691222088   Max.   :6.023e+09   Max.   :77423000  
 NA's   :368072       NA's   :363830       NA's   :362741      NA's   :399444    
    WC03263              WC03351              WC03426         
 Min.   :-132131400   Min.   :-2.368e+06   Min.   :-56852690  
 1st Qu.:     -6938   1st Qu.: 2.776e+05   1st Qu.:        0  
 Median :         0   Median : 9.639e+05   Median :        0  
 Mean   :    289751   Mean   : 3.694e+07   Mean   :   520920  
 3rd Qu.:     14754   3rd Qu.: 3.846e+06   3rd Qu.:    40772  
 Max.   : 787000000   Max.   : 3.370e+10   Max.   :564000000  
 NA's   :367161       NA's   :361845       NA's   :367555     
    WC03451             WC03480             WC03495          
 Min.   :        0   Min.   :-23657100   Min.   :-8.446e+08  
 1st Qu.:        0   1st Qu.:    80000   1st Qu.: 2.336e+04  
 Median :        0   Median :   214018   Median : 1.976e+05  
 Mean   :    71573   Mean   :  1631472   Mean   : 6.377e+06  
 3rd Qu.:        0   3rd Qu.:   610100   3rd Qu.: 8.771e+05  
 Max.   :521400000   Max.   :637333000   Max.   : 1.154e+10  
 NA's   :368921      NA's   :379825      NA's   :382312      
    WC03501              WC03995              WC03999             WC04055        
 Min.   :-7.276e+08   Min.   :-7.276e+08   Min.   :0.000e+00   Min.   :-3712984  
 1st Qu.: 3.092e+05   1st Qu.: 3.124e+05   1st Qu.:7.533e+05   1st Qu.:       0  
 Median : 9.146e+05   Median : 9.211e+05   Median :2.103e+06   Median :       0  
 Mean   : 1.001e+07   Mean   : 1.008e+07   Mean   :4.753e+07   Mean   :   15518  
 3rd Qu.: 2.784e+06   3rd Qu.: 2.799e+06   3rd Qu.:7.024e+06   3rd Qu.:       0  
 Max.   : 1.163e+10   Max.   : 1.163e+10   Max.   :3.605e+10   Max.   :29963781  
 NA's   :361840       NA's   :361846       NA's   :361844      NA's   :435106    
    WC04100             WC04101             WC04199             WC04201          
 Min.   :-175437.0   Min.   :-49891826   Min.   :-49891826   Min.   :-349044000  
 1st Qu.:      0.0   1st Qu.:    -2250   1st Qu.:    -3253   1st Qu.:     26544  
 Median :      0.0   Median :        0   Median :       -8   Median :    117804  
 Mean   :    -13.6   Mean   :    -6615   Mean   :    -7622   Mean   :   2120565  
 3rd Qu.:      0.0   3rd Qu.:        0   3rd Qu.:        0   3rd Qu.:    466311  
 Max.   :   1169.0   Max.   : 70593681   Max.   : 70593681   Max.   :1856720000  
 NA's   :446666      NA's   :422283      NA's   :427220      NA's   :362689      
    WC04251             WC04551             WC04601              WC04751         
 Min.   : -2500625   Min.   :  -127635   Min.   :   -930158   Min.   : -3369220  
 1st Qu.:        0   1st Qu.:        0   1st Qu.:     16436   1st Qu.:        0  
 Median :        0   Median :     9600   Median :     78765   Median :        0  
 Mean   :   312957   Mean   :   467540   Mean   :   1549868   Mean   :    81799  
 3rd Qu.:        0   3rd Qu.:    71962   3rd Qu.:    326236   3rd Qu.:        0  
 Max.   :676933000   Max.   :242430200   Max.   :1641024000   Max.   :659223000  
 NA's   :383305      NA's   :380532      NA's   :366555       NA's   :392452     
    WC04825              WC04826              WC04827          
 Min.   :-1.281e+10   Min.   :-236366500   Min.   :-2.112e+08  
 1st Qu.:-2.155e+05   1st Qu.:   -112943   1st Qu.:-1.424e+04  
 Median :-3.660e+04   Median :    -13297   Median : 1.665e+04  
 Mean   :-7.985e+05   Mean   :   -286788   Mean   : 6.527e+05  
 3rd Qu.: 7.202e+03   3rd Qu.:      7670   3rd Qu.: 1.492e+05  
 Max.   : 1.918e+08   Max.   : 224005900   Max.   : 1.346e+10  
 NA's   :393848       NA's   :398645       NA's   :399572      
    WC04828             WC04829            WC04830              WC04860          
 Min.   :-49857150   Min.   :-7793000   Min.   :-604000000   Min.   :-556060000  
 1st Qu.:        0   1st Qu.:       0   1st Qu.:     -6678   1st Qu.:     -5714  
 Median :        0   Median :       0   Median :         0   Median :     59911  
 Mean   :   202071   Mean   :   11571   Mean   :    -80392   Mean   :   1820530  
 3rd Qu.:      142   3rd Qu.:     454   3rd Qu.:     16910   3rd Qu.:    318090  
 Max.   :318390000   Max.   :15050000   Max.   :1097000000   Max.   :2058000000  
 NA's   :451407      NA's   :451741     NA's   :420744       NA's   :366103      
    WC05001             WC05101             WC05202            WC05301         
 Min.   :        0   Min.   :      0.0   Min.   :-1578480   Min.   :0.000e+00  
 1st Qu.:        5   1st Qu.:      0.0   1st Qu.:       0   1st Qu.:2.152e+04  
 Median :       13   Median :      0.0   Median :       0   Median :1.860e+05  
 Mean   :     6622   Mean   :     41.1   Mean   :    1526   Mean   :5.875e+06  
 3rd Qu.:       47   3rd Qu.:      0.3   3rd Qu.:       3   3rd Qu.:6.012e+05  
 Max.   :150199550   Max.   :1802608.0   Max.   :50299501   Max.   :1.003e+11  
 NA's   :369692      NA's   :369450      NA's   :361827     NA's   :362358     
       WC05350          WC05376             WC05401            WC05476         
           :128388   Min.   :  -127635   Min.   :       0   Min.   :-11284204  
 31.12.2014:  4319   1st Qu.:        0   1st Qu.:       0   1st Qu.:        2  
 31.12.2015:  4289   Median :     6492   Median :       0   Median :        5  
 31.12.2016:  4243   Mean   :   443107   Mean   :    1029   Mean   :     9007  
 31.12.2017:  4192   3rd Qu.:    64000   3rd Qu.:       0   3rd Qu.:       38  
 (Other)   : 81372   Max.   :242430200   Max.   :16060700   Max.   :159986993  
 NA's      :232695   NA's   :376194      NA's   :365099     NA's   :362557     
    WC05491             WC05505           WC05508             WC05651         
 Min.   :-11284204   Min.   :    -74   Min.   :     -900   Min.   :        0  
 1st Qu.:        2   1st Qu.:      0   1st Qu.:        2   1st Qu.:        0  
 Median :        5   Median :      0   Median :        7   Median :    68490  
 Mean   :     8786   Mean   :    407   Mean   :     9000   Mean   :  2061175  
 3rd Qu.:       38   3rd Qu.:      3   3rd Qu.:       76   3rd Qu.:   327368  
 Max.   :159986993   Max.   :6814949   Max.   :146841160   Max.   :119768705  
 NA's   :362529      NA's   :367142    NA's   :361813      NA's   :459388     
       WC05905          WC06798          WC07011          WC07210         
           :128388   Min.   : NA      Min.   :     0   Min.   :0.000e+00  
 30.05.2017:   629   1st Qu.: NA      1st Qu.:   645   1st Qu.:2.037e+04  
 30.05.2018:   626   Median : NA      Median :  1574   Median :2.003e+05  
 14.07.2003:   583   Mean   :NaN      Mean   :  5642   Mean   :1.299e+06  
 30.05.2016:   520   3rd Qu.: NA      3rd Qu.:  3891   3rd Qu.:7.577e+05  
 (Other)   : 89056   Max.   : NA      Max.   :685000   Max.   :1.845e+09  
 NA's      :239696   NA's   :459498   NA's   :399221   NA's   :381008     
    WC08001             WC08002             WC08004             WC08006        
 Min.   :0.000e+00   Min.   :0.000e+00   Min.   :0.000e+00   Min.   :       0  
 1st Qu.:6.978e+05   1st Qu.:5.022e+05   1st Qu.:2.480e+05   1st Qu.:       0  
 Median :2.602e+06   Median :2.299e+06   Median :1.361e+06   Median :  162668  
 Mean   :2.249e+07   Mean   :2.128e+07   Mean   :1.230e+07   Mean   : 1397315  
 3rd Qu.:7.832e+06   3rd Qu.:7.265e+06   3rd Qu.:4.594e+06   3rd Qu.:  744360  
 Max.   :8.104e+09   Max.   :8.104e+09   Max.   :3.757e+09   Max.   :33165328  
 NA's   :381011      NA's   :376629      NA's   :405161      NA's   :459430    
    WC08256          WC08301            WC08326              WC08371        
 Min.   :-13.5    Min.   :-31552.6   Min.   :-1941550.0   Min.   :-47037.4  
 1st Qu.:  0.0    1st Qu.:     2.2   1st Qu.:       2.1   1st Qu.:     2.2  
 Median :  2.3    Median :     8.7   Median :       5.3   Median :     8.6  
 Mean   : 16.4    Mean   :     4.2   Mean   :     -17.7   Mean   :     5.4  
 3rd Qu.: 26.8    3rd Qu.:    17.0   3rd Qu.:       9.4   3rd Qu.:    16.9  
 Max.   :100.0    Max.   : 15479.0   Max.   :    7053.0   Max.   :194230.1  
 NA's   :386427   NA's   :375174     NA's   :372069       NA's   :375847    
    WC18100              WC18155              WC18191          
 Min.   :-1.068e+09   Min.   :-400514700   Min.   :-288394000  
 1st Qu.: 1.328e+06   1st Qu.:     34812   1st Qu.:     33486  
 Median : 3.838e+06   Median :    139952   Median :    123501  
 Mean   : 3.289e+07   Mean   :   2479550   Mean   :   2230634  
 3rd Qu.: 1.113e+07   3rd Qu.:    566846   3rd Qu.:    504499  
 Max.   : 9.571e+09   Max.   :1952666000   Max.   :1612885000  
 NA's   :386752       NA's   :367883       NA's   :366119      
    WC18198          
 Min.   :-230612000  
 1st Qu.:     55925  
 Median :    182969  
 Mean   :   2986076  
 3rd Qu.:    712489  
 Max.   :1947870000  
 NA's   :368753      

TO DO’s:

• Calculate Benchmark (market-cap weighted big stocks of the BRIC region)
• Calculate break points on big stocks
• decide on strategy and implement it
• calculate portfolio characteristics
• visualize and showcase strategy performance
• regress strategy PF on FF5FM and momentum for style exposure analysis
• Literature

Project Dates: Thesis submission: June 21 Final presentation: June 07

Strategy: GDP weighted countries; stock level: max sharpe ratio, min volatility, equal sector weights or quotas, momentum?

EDA

summary(BRIC.monthly$ym)

   Min. 1st Qu.  Median    Mean 3rd Qu.    Max. 
   1980    1990    1999    1999    2009    2019 

# finding the number of NA values by column in the dataframe
BRIC.monthly %>% mutate_all(is.na) %>% summarize_all(sum)

NA

# finding the number of NA values by column in the dataframe
BRIC.static %>% mutate_all(is.na) %>% summarize_all(sum)

To DO: compute correlations by sector (10 sectors)

# regular correlation matrix of all (four) numeric attributes
cor(select(BRIC.static, where(is.numeric)))

the standard deviation is zero

        ICBSUC INDG WC06011 WC07021 WC07022 WC07023 WC07024 WC07025 WC07026
ICBSUC       1   NA      NA      NA      NA      NA      NA      NA      NA
INDG        NA    1      NA      NA      NA      NA      NA      NA      NA
WC06011     NA   NA       1      NA      NA      NA      NA      NA      NA
WC07021     NA   NA      NA       1      NA      NA      NA      NA      NA
WC07022     NA   NA      NA      NA       1      NA      NA      NA      NA
WC07023     NA   NA      NA      NA      NA       1      NA      NA      NA
WC07024     NA   NA      NA      NA      NA      NA       1      NA      NA
WC07025     NA   NA      NA      NA      NA      NA      NA       1      NA
WC07026     NA   NA      NA      NA      NA      NA      NA      NA       1
WC07027     NA   NA      NA      NA      NA      NA      NA      NA      NA
WC07028     NA   NA      NA      NA      NA      NA      NA      NA      NA
WC07041     NA   NA      NA      NA      NA      NA      NA      NA      NA
WC18272     NA   NA      NA      NA      NA      NA      NA      NA      NA
        WC07027 WC07028 WC07041 WC18272
ICBSUC       NA      NA      NA      NA
INDG         NA      NA      NA      NA
WC06011      NA      NA      NA      NA
WC07021      NA      NA      NA      NA
WC07022      NA      NA      NA      NA
WC07023      NA      NA      NA      NA
WC07024      NA      NA      NA      NA
WC07025      NA      NA      NA      NA
WC07026      NA      NA      NA      NA
WC07027       1      NA      NA      NA
WC07028      NA       1      NA      NA
WC07041      NA      NA       1      NA
WC18272      NA      NA      NA       1

# number of entries stocks per country
tabulate(as.factor(BRIC.monthly$country));

[1]  221368 1736707 3082737  484946

unique(BRIC.monthly$country)

[1] "BRA" "CHN" "RUS" "IND"

#weight CHN 

# unqiue industries
unique(BRIC.static$INDM)

  [1] "Iron & Steel"          "Broadline Retailers"   "Alt. Electricity"     
  [4] "Con. Electricity"      "Dur. Household Prod."  "Aerospace"            
  [7] "Auto Parts"            "Banks"                 "Food Products"        
 [10] "Automobiles"           "Commodity Chemicals"   "Paper"                
 [13] "Farm Fish Plantation"  "Specialty Chemicals"   "Industrial Machinery" 
 [16] "Fixed Line Telecom."   "Divers. Industrials"   "Alternative Fuels"    
 [19] "Unclassified"          "Consumer Electronics"  "Brewers"              
 [22] "Prop. & Casualty Ins." "Clothing & Accessory"  "Specialty Finance"    
 [25] "Computer Hardware"     "Comm. Vehicles,Trucks" "Real Estate Hold, Dev"
 [28] "Exploration & Prod."   "Nonferrous Metals"     "Footwear"             
 [31] "Electrical Equipment"  "Electronic Equipment"  "Apparel Retailers"    
 [34] "Airlines"              "Multiutilities"        "Integrated Oil & Gas" 
 [37] "Unquoted equities"     "Gas Distribution"      "Building Mat.& Fix."  
 [40] "Healthcare Providers"  "Home Construction"     "Media Agencies"       
 [43] "Medical Equipment"     "Railroads"             "Full Line Insurance"  
 [46] "Recreational Services" "Food Retail,Wholesale" "Transport Services"   
 [49] "Mobile Telecom."       "Hotels"                "Recreational Products"
 [52] "Furnishings"           "Business Support Svs." "Drug Retailers"       
 [55] "Heavy Construction"    "Water"                 "Tobacco"              
 [58] "Spec.Consumer Service" "Oil Equip. & Services" "Internet"             
 [61] "Biotechnology"         "Ind. & Office REITs"   "Diversified REITs"    
 [64] "Retail REITs"          "Personal Products"     "Software"             
 [67] "Financial Admin."      "Real Estate Services"  "Medical Supplies"     
 [70] "Consumer Finance"      "Investment Services"   "Pharmaceuticals"      
 [73] "Asset Managers"        "Computer Services"     "Industrial Suppliers" 
 [76] "Insurance Brokers"     "Restaurants & Bars"    "Specialty Retailers"  
 [79] "Travel & Tourism"      "Residential REITs"     "Coal"                 
 [82] "Soft Drinks"           "Renewable Energy Eq."  "Mortgage REITs"       
 [85] "Specialty REITs"       "Distillers & Vintners" "Containers & Package" 
 [88] "Telecom. Equipment"    "Aluminum"              "Nondur.Household Prod"
 [91] "Marine Transportation" "Publishing"            "Trucking"             
 [94] "Tires"                 "Home Improvement Ret." "Waste, Disposal Svs." 
 [97] "Semiconductors"        "Broadcast & Entertain" "General Mining"       
[100] "Delivery Services"     "Forestry"              "Diamonds & Gemstones" 
[103] "Toys"                  "Pipelines"             "Defense"              
[106] "Plat.& Precious Metal" "Gold Mining"           "Life Insurance"       
[109] "Bus.Train & Employmnt" "Elec. Office Equip."   "Mortgage Finance"     
[112] "Electricity"           "Investment Trusts"     "Mutual Funds"         
[115] "Reinsurance"          

# Real Estate: Residential REITs, Retail RETS, Diversified REITS, Mortgage REITS, Specialty REITS, Ind. & Office REITS
# Commodities: Iron & Steel, Commodity Chemicals, General Mining, Integrated Oil and Gas, Alternative Fuels, 
# Infrastructure: Railroads, Transportation Services, Alternative Fuels, Alt. Electricity

# cross tab plot of ownership interest and certain features with legend
# cex.names: expansion factor for the axis labels
barplot(with(BRIC.static, table(INDM)), beside = TRUE, legend.text = TRUE, main = "Crosstab", cex.names = 0.4)

# Market Cap values
summary(BRIC.monthly$MV.USD)

    Min.  1st Qu.   Median     Mean  3rd Qu.     Max.     NA's 
0.00e+00 3.00e+00 4.40e+01 1.14e+05 4.30e+02 2.90e+10  4035736 

summary(BRIC.monthly$RET.USD)

   Min. 1st Qu.  Median    Mean 3rd Qu.    Max.    NA's 
   -100      -8       0       2       8     903 4044505 

Some conventions:

Characteristic should be calculated as in Hanauer & Lauterbach (2019) or in Hanauer (2020)

Big stocks should be defined as the biggest stocks which together account for 90% of a country’s aggregated market capitalization Benchmark should be defined as the cap-weighted universe of big stocks Returns should be in USD Breakpoints (for Fama-French factors) should be calculated on big stocks (as in the excursus) but both small and big stocks go into the factor calculation.

Benchmark Calculation

length(BRIC.static$Id)

[1] 11783

length(BRIC.yearly$Id)

[1] 459498

# joining the monthly and the static dataframe by the Id column (retainig all rows of BRIC.yearly)
df_combined <- left_join(x=BRIC.monthly,y=BRIC.static,by="Id")


head(df_combined)

NA
NA
NA

summary(df_combined$MV.USD)

    Min.  1st Qu.   Median     Mean  3rd Qu.     Max.     NA's 
0.00e+00 3.00e+00 4.40e+01 1.14e+05 4.30e+02 2.90e+10  4035736 

dim(df_combined)

[1] 5525758      53

dim(BRIC.yearly)

[1] 459498    105

dim(BRIC.static)

[1] 11783    41

dim(BRIC.monthly)

[1] 5525758      13

## finding the number of NA values by column in the dataframe
BRIC.yearly %>% mutate_all(is.na) %>% summarize_all(sum)

# markt cap (USD): WC07210

## finding the number of NA values by column in the dataframe
df_combined %>% mutate_all(is.na) %>% summarize_all(sum)

Idea: calculate 90% of the sum of all stock market caps per country as a time series (store the result in a column )

# Big stocks should be defined as the biggest stocks which together account for 90% of # a country's aggregated market capitalization


# grouping the mcap values of individual stocks by country
mcap_country <- df_combined %>%
  filter(df_combined$MV.USD != "NA") %>% 
  group_by(country.x, ym) 

# computing the sum of the individual stock's market caps by country
country_mcap <- mcap_country %>%
group_by(country.x, ym) %>%
    summarize(mc_sum = sum(MV.USD))

`summarise()` regrouping output by 'country.x' (override with `.groups` argument)

country_mcap

NA
NA

summary(country_mcap$mc_sum[country_mcap$country.x == "BRA"])

     Min.   1st Qu.    Median      Mean   3rd Qu.      Max. 
2.330e+04 6.315e+04 3.933e+05 4.880e+08 6.463e+05 2.905e+10 

summary(country_mcap$mc_sum[country_mcap$country.x == "RUS"])

     Min.   1st Qu.    Median      Mean   3rd Qu.      Max. 
     79.3   34207.6  349186.3  379928.7  656237.2 1327783.7 

summary(country_mcap$mc_sum[country_mcap$country.x == "IND"])

     Min.   1st Qu.    Median      Mean   3rd Qu.      Max. 
     52.1   29452.4  137142.8  517561.0 1071833.0 2427939.2 

summary(country_mcap$mc_sum[country_mcap$country.x == "CHN"])

   Min. 1st Qu.  Median    Mean 3rd Qu.    Max. 
    200  204941  535943 2143131 3640033 9920863 

# sorting the stocks by market cap and extracting the ISINs of the biggest ones that make up 90% of a country's market cap

# RUSSIA
# 90% of the market cap in Russia: 
0.9*country_mcap$mc_sum[4]

[1] 2.4107e+10

# subsetting all Russian stocks
russian_stocks <- as.data.frame(mcap_country) %>%
   filter(country.x == "RUS") 

# sorting the dataframe of russian stocks by decreasing market cap
russian_stocks <- russian_stocks[order(russian_stocks$WC07210,decreasing = TRUE),]

Error in order(russian_stocks$WC07210, decreasing = TRUE) : 
  argument 1 is not a vector

idea:

big_stocks <- mcap_country %>% select(ISIN) %>% sort(mcap_country\(WC07210, decreasing=T) %>% filter(mcap_country\)WC07210 <= 0.9*country_mcap$mc_sum)

big_stocks

### Attempt ###


## Determine portfolio breakpoints for Size
# Fama-French take the MV from end-of-June and rebalance yearly
df_combined[,month := month(Date)]
df_combined[,year := year(Date)]
df_combined[,hcjun := ifelse(month>=7,year,year-1)]

# determine size portfolio allocation from July on using data that's public from end-of-June on
setorder(df_combined,Date,-MV.USD)
hlpvariable <-  df_combined[month==7 & !is.na(MV.USD),
                .(pf.size = ifelse((cumsum(MV.USD)/sum(MV.USD))>=0.9,"Small","Big"),Id),
                               by=year]

# Merge the size portfolio allocation back from July Y to June Y+1
panel_country <- merge(df_combined,hlpvariable,
                       by.x=c("hcjun","Id"),
                       by.y=c("year","Id"),
                       all.x=T)

panel_country

### Attempt end ###

Calculating the excess return of the market portfolio (RMRF)

summary(BRIC.monthly$MV.USD)

    Min.  1st Qu.   Median     Mean  3rd Qu.     Max.     NA's 
0.00e+00 3.00e+00 4.40e+01 1.14e+05 4.30e+02 2.90e+10  4035736 

summary(BRIC.monthly$RET.USD)

   Min. 1st Qu.  Median    Mean 3rd Qu.    Max.    NA's 
   -100      -8       0       2       8     903 4044505 

# market beta regression
my.ols <- lm(data=BRIC.monthly, formula = RET.USD ~ MV.USD)

summary(my.ols)

Call:
lm(formula = RET.USD ~ MV.USD, data = BRIC.monthly)

Residuals:
    Min      1Q  Median      3Q     Max 
-101.76   -9.72   -1.94    5.75  901.21 

Coefficients:
              Estimate Std. Error t value Pr(>|t|)    
(Intercept)  1.783e+00  1.918e-02  92.951   <2e-16 ***
MV.USD      -1.649e-06  1.052e-06  -1.567    0.117    
---
Signif. codes:  0 ‘***’ 0.001 ‘**’ 0.01 ‘*’ 0.05 ‘.’ 0.1 ‘ ’ 1

Residual standard error: 23.27 on 1475669 degrees of freedom
  (4050087 observations deleted due to missingness)
Multiple R-squared:  1.664e-06, Adjusted R-squared:  9.866e-07 
F-statistic: 2.456 on 1 and 1475669 DF,  p-value: 0.1171

  
# isolate beta
my.beta <- coef(my.ols)[2]

my.beta

       MV.USD 
-1.648943e-06 

Calculating the size portfolios